# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
# 作者：cacho_37967865
# 博客：https://blog.csdn.net/sinat_37967865
# 文件：get_stock_loan.py
# 日期：2019-03-05
# 备注：获取东方财富网的可转债交易信息存入到loanTrade_to_mysql()和可转债基本信息存入到loan_to_mysql()    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import requests
import time
import threading
from datetime import datetime

from pacong.stock.stock_config import db,cursor,headers
from pycacho.cachobase.logger import Logger

logger = Logger("changedLoan").get_log()

lock = threading.Lock()


def createTable():
    sql1 = 'drop table if exists loan_baseInfo;'  # ,
    sql2 = 'create table loan_baseInfo (loan_type VARCHAR(10),loan_no VARCHAR(10),loan_name VARCHAR(10),stock_no VARCHAR(10),stock_name VARCHAR(10),' \
           'PRIMARY KEY (loan_no));'
    sql3 = 'create table loan_tradeInfo (trade_date VARCHAR(20),loan_no VARCHAR(10),loan_name VARCHAR(10),stock_no VARCHAR(10),stock_name VARCHAR(10),' \
           'loan_price float,loan_chg float,stock_price float,stock_priceChg float,change_price float,change_value float,change_rate float,loan_rate float,buyBack_price float,atone_price float,' \
           'loanDue_price float,loan_value float,apply_date VARCHAR(20),market_date VARCHAR(20),change_date VARCHAR(20),' \
           'PRIMARY KEY (trade_date,loan_no));'
    cursor.execute(sql1)
    cursor.execute(sql2)
    #cursor.execute(sql3)
    db.commit()

# 获取可转债交易信息
def get_changedLoan(p):
    url = 'http://nufm.dfcfw.com/EM_Finance2014NumericApplication/JS.aspx?' \
          'type=CT&token=4f1862fc3b5e77c150a2b985b12db0fd&cmd=C._DEBT_EXT_Z&sty=FC20CDTA&st=(BAdinSTARTDATE)&p={0}'.format(p)
    print(url)
    resp = requests.get(url, headers=headers)
    trade_date = datetime.now().strftime('%Y%m%d')
    data1 = resp.text
    data = tuple(eval(data1))   # 元组格式的字符串转换为元组类型
    #print(type(data), data)
    for loan_info in data:
        loan = loan_info.split(',')
        #print(loan)
        loan_type = loan[0]             # 转债交易所（1-沪市，2-深市）
        loan_no = loan[1]
        loan_name = loan[2]
        if loan[3] == '-':
            loan_price = 0.0
        else:
            loan_price = float(loan[3])    # 债券交易价格
        if loan[4] == '-':
            loan_chg = 0.0
        else:
            loan_chg = float(loan[4])    # 债券交易价格
        stock_no = loan[6]
        stock_name = loan[7]
        stock_price = float(loan[8])        # 正股价
        stock_priceChg = float(loan[9])     # 正股价涨跌
        change_price = float(loan[10])      # 转股价
        change_value = float(loan[11])      # 转股价值=正股价格÷转股价×100
        change_rate = float(loan[12])       # 转股溢价率=债现价/转股价值-100%(一般为正数，如果是负数，就可以套现，越低越好)
        loan_rate = float(loan[13])         # 纯债溢价率=（可转债价格-纯债价值）/纯债价值
        if loan[14] == '-':
            buyBack_price = 0.0
        else:
            buyBack_price = float(loan[14]) # 回售触发价：触发回售的正股价格(比转股价低好多)
        atone_price = float(loan[15])       # 强赎触发价：触发强赎的正股价格(比转股价高好多)
        loanDue_price = float(loan[16])     # 到期赎回价：债券到期后公司买回的价格,一般这个越高，纯债价值也越高
        loan_value = float(loan[17])        # 纯债价值：可转债只能到期还钱的情况下，把它当做一支纯债时我们能接受的价格
        apply_date = loan[20]               # 纯债申购日期
        market_date = loan[19]              # 纯债上市交易日期(一般申购日期后1个月可以交易)
        change_date = loan[18]              # 纯债可以转换为股票的日期(一般申购日期后6个月可以转)

        with lock:
            loan_to_mysql(loan_type, loan_no, loan_name, stock_no, stock_name)
            loanTrade_to_mysql(trade_date, loan_no, loan_name, stock_no, stock_name, loan_price, loan_chg,stock_price,stock_priceChg, change_price,change_value,
                               change_rate, loan_rate,buyBack_price, atone_price, loanDue_price, loan_value,apply_date, market_date, change_date)

# 获取可转债交易信息
def get_changed_loan(p):
    url = 'http://90.push2.eastmoney.com/api/qt/clist/get?pn=1&pz={0}&fltt=2&' \
          'fs=b:MK0354&fields=f1,f2,f3,f12,f13,f14,f227,f229,f230,f232,f233,f234,f235,f236,f237,f238,f239,f240,f241,f242,f26,f243'.format(p)
    resp = requests.get(url, headers=headers)
    trade_date = datetime.now().strftime('%Y%m%d')
    data = resp.json()['data']
    #print(data)
    n = data['total']
    for i in range(n):
        loan_info = data['diff'][str(i)]
        #print(loan_info)
        loan_type = loan_info['f1']  # 转债交易所（2-沪市，3-深市）
        loan_no = loan_info['f12']
        loan_name = loan_info['f14']
        loan_price = loan_info['f2']  # 债券交易价格
        loan_chg = loan_info['f3']    # 债券价格涨跌幅
        stock_no = loan_info['f232']
        stock_name = loan_info['f234']
        stock_price = loan_info['f229']     # 正股价
        stock_priceChg = loan_info['f230']  # 正股价涨跌
        stock_type = loan_info['f233']      # 正股交易所（1-沪市，0-深市）
        change_price = loan_info['f235']    # 转股价
        change_value = loan_info['f236']    # 转股价值=正股价格÷转股价×100
        change_rate = loan_info['f237']     # 转股溢价率=债现价/转股价值-100%(一般为正数，如果是负数，就可以套现，越低越好)
        loan_rate = loan_info['f238']       # 纯债溢价率=（可转债价格-纯债价值）/纯债价值
        buyBack_price = loan_info['f239']   # 回售触发价：触发回售的正股价格(比转股价低好多)
        atone_price = loan_info['f240']     # 强赎触发价：触发强赎的正股价格(比转股价高好多)
        loanDue_price = loan_info['f241']   # 到期赎回价：债券到期后公司买回的价格,一般这个越高，纯债价值也越高
        loan_value = loan_info['f227']       # 纯债价值：可转债只能到期还钱的情况下，把它当做一支纯债时我们能接受的价格
        apply_date = loan_info['f243']      # 纯债申购日期
        market_date = loan_info['f26']      # 纯债上市交易日期(一般申购日期后1个月可以交易)
        change_date = loan_info['f242']     # 纯债可以转换为股票的日期(一般申购日期后6个月可以转)
        loan_to_mysql(loan_type, loan_no, loan_name, stock_no, stock_name)
        loanTrade_to_mysql(trade_date, loan_no, loan_name, stock_no, stock_name, loan_price, loan_chg,stock_price,stock_priceChg, change_price,change_value,
                               change_rate, loan_rate,buyBack_price, atone_price, loanDue_price, loan_value,apply_date, market_date, change_date)



# 将可转债基本信息（债券类型（1-沪市，2-深市），债券代码，债券简称）
def loan_to_mysql(loan_type,loan_no,loan_name,stock_no,stock_name):
    sql = "insert into loan_baseInfo (loan_type,loan_no,loan_name,stock_no,stock_name) values ('%s','%s','%s','%s','%s');" \
          % (loan_type,loan_no,loan_name,stock_no,stock_name)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('%s可转债基本信息插入成功！', loan_no)


# 将可转债基本信息（债券类型（1-沪市，2-深市），债券代码，债券简称）
def loanTrade_to_mysql(trade_date, loan_no, loan_name, stock_no, stock_name, loan_price, loan_chg,stock_price,stock_priceChg,change_price,change_value,
                       change_rate, loan_rate,buyBack_price, atone_price, loanDue_price, loan_value,apply_date, market_date, change_date):
    sql = "insert into loan_tradeInfo (trade_date, loan_no, loan_name, stock_no, stock_name, loan_price,loan_chg, stock_price,stock_priceChg,change_price,change_value," \
          "change_rate, loan_rate,buyBack_price, atone_price, loanDue_price, loan_value,apply_date, market_date, change_date)" \
          " values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
          % (trade_date, loan_no, loan_name, stock_no, stock_name, loan_price,loan_chg, stock_price,stock_priceChg,change_price,change_value,
             change_rate, loan_rate,buyBack_price, atone_price, loanDue_price, loan_value,apply_date, market_date, change_date)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('%s可转债交易信息插入成功！', loan_no)


def main():
    createTable()
    threads = [threading.Thread(target=get_changedLoan, args=(t,)) for t in range(1, 30)]     # 第1~3页
    for t in threads:
        t.start()

    for t in threads:
        t.join()

if __name__ == '__main__':
    cTime = time.time()
    #main()
    createTable()
    get_changed_loan(500)
    logger.info("总共耗时：%s ", time.time() - cTime)